1. Field of the Invention
This invention relates to a method of analysis and of increasing the speed at which the condition of a database is determined. The invention is more particularly related to the determination of appropriate data needed for a comprehensive analysis of specific tables in the database, and to increase the speed at which the analysis for determining the condition of a database table is performed.
2. Discussion of the Background
With the increasing use of computer databases in each of the home, office, and scientific environments, computer users and Database Administrators (DBA's) are increasingly confronted with the tasks of determining the condition or health of databases and performing various operations to maintain the database, including reorganization or rebuilding of tables stored in the database. Before rebuilding a database table(s), the DBA must first determine whether the condition of the database table(s) requires rebuilding.
Typically, commercial database products include tools to perform analysis to determine the condition of a database. For example, Oracle database management systems include an analyze function that performs a predetermined function including an SQL call UPDATE.sub.-- STATISTICS.sub.-- FOR.sub.-- TABLE. The analyze function reads a selected database table and generates a set of statistics that reveal the condition of the table.
FIG. 1 is a flowchart illustrating the basic steps performed by a conventional Database Management System (DBMS) for determining the condition of a database table. At step 10, a Database Administrator (DBA) provides the name of a table to be analyzed. The table name is provided to a DBMS function that returns data about the named table (step 20). The returned data is analyzed resulting in statistical information (step 30) which is then placed (stored) in a location for retrieval for by DBA (step 40).
The DBMS function that returns data (DBMS analysis function) is shown in FIG. 2 and includes the steps of building an SQL call (step 50), invoking the SQL call (step 60), performing SQL overhead and administrative functions (step 70), and returning the SQL results (step 80). The SQL results are provided as the return values (statistical information) in step 30 of FIG. 1.
Once the SQL results are analyzed, the displayed (or stored) information is utilized by the DBA to determine appropriate action, if needed, to properly maintain the database (rebuild tables, for example). In the example function, UPDATE.sub.-- STATISTICS.sub.-- FOR.sub.-- TABLE, the Oracle DBMS provides table information including No. of Rows, Chained Rows, Blocks allocated, Blocks allocated but free, Blocks on freelist, Average available space in Freelist, Average free space in a Block, and Min, Max, Average Row Length.
However, the provided table information does not provide enough information to give the DBA the most accurate picture of the DBMS table condition. In addition, current methods for retrieving the DBMS table information are slow, being encumbered by overhead associated with standard DBMS procedures and function calls (file reading techniques, and SQL, for example).
Modern database tables contain enormous amounts of data. If a DBA needs to determine the condition of a large database table, analysis techniques based on current methods can take unacceptably large amounts of time during which a database table may not be available to a user. Furthermore, current methods do not provide the DBA with the exact information needed for a proper analysis. Therefore, the DBA may rebuild tables when it is not necessary or where other action may have been more appropriate. All of the above leads to increased down time and maintenance efforts.